package com.lsy.demo;
/**
 * @author : lsy
 * @version 1.0
 * @description: TODO
 * @date 2021/10/18 13:25
 */

import cn.gjing.tools.excel.ExcelField;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import lombok.Data;
import lombok.EqualsAndHashCode;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * @description: 测试类
 * @author: chenmingjian
 * @date: 19-4-4 15:24
 */
@SpringBootTest
@RunWith(SpringRunner.class)
public class Test {

    /**
     * 读取少于1000行的excle
     */
    @org.junit.Test
    public void readLessThan1000Row(){
        /*String filePath = "D:\\wenan\\123.xlsx";
        List<Object> objects = ExcelUtil.readLessThan1000Row(filePath);
        objects.forEach(System.out::println);*/
        String filePath = "D:\\wenan\\111.xlsx";
        Sheet sheet = new Sheet(1, 3);
        List<Object> objects = ExcelUtil.readLessThan1000RowBySheet(filePath,sheet);
        for (Object sdd : objects) {
            System.out.println(sdd);
        }
    }

    /**
     * 可用===
     * 读取少于1000行的excle，可以指定sheet和从几行读起
     */
    @org.junit.Test
    public void readLessThan1000RowBySheet(){
        String filePath = "D:\\wenan\\111.xlsx";
        Sheet sheet = new Sheet(1, 3);
        List<Object> objects = ExcelUtil.readLessThan1000RowBySheet(filePath,sheet);
        for (Object sdd : objects) {
            System.out.println(sdd);
        }



        objects.forEach(System.out::println);
        System.out.println("===================11111======================");
        Sheet sheet1 = new Sheet(2, 3);
        List<Object> objects1 = ExcelUtil.readLessThan1000RowBySheet(filePath,sheet1);
        objects1.forEach(System.out::println);
        System.out.println("===================22222======================");
        Sheet sheet2 = new Sheet(3, 2);
        List<Object> objects2 = ExcelUtil.readLessThan1000RowBySheet(filePath,sheet2);
        objects2.forEach(System.out::println);
        System.out.println("===================33333======================");
    }

    /**
     * 读取大于1000行的excle
     * 带sheet参数的方法可参照测试方法readLessThan1000RowBySheet()
     */
    @org.junit.Test
    public void readMoreThan1000Row(){
        String filePath = "/home/chenmingjian/Downloads/测试.xlsx";
        List<Object> objects = ExcelUtil.readMoreThan1000Row(filePath);
        objects.forEach(System.out::println);
    }


    /**
     * 生成excle
     * 带sheet参数的方法可参照测试方法readLessThan1000RowBySheet()
     */
    @org.junit.Test
    public void writeBySimple(){
        String filePath = "D:\\wenan\\123.xlsx";
        List<List<Object>> data = new ArrayList<>();
        data.add(Arrays.asList("111","222","333"));
        data.add(Arrays.asList("111","222","333"));
        data.add(Arrays.asList("111","222","333"));
        List<String> head = Arrays.asList("表头1", "表头2", "表头3");
        ExcelUtil.writeBySimple(filePath,data,head);
    }


    /**
     * 生成excle, 带用模型
     * 带sheet参数的方法可参照测试方法readLessThan1000RowBySheet()
     */
    @org.junit.Test
    public void writeWithTemplate(){
        String filePath = "/home/chenmingjian/Downloads/测试.xlsx";
        ArrayList<TableHeaderExcelProperty> data = new ArrayList<>();
        for(int i = 0; i < 4; i++){
            TableHeaderExcelProperty tableHeaderExcelProperty = new TableHeaderExcelProperty();
            tableHeaderExcelProperty.setName("cmj" + i);
            tableHeaderExcelProperty.setAge(22 + i);
            tableHeaderExcelProperty.setSchool("清华大学" + i);
            data.add(tableHeaderExcelProperty);
        }
        ExcelUtil.writeWithTemplate(filePath,data);
    }


    /**
     * 生成excle, 带用模型,带多个sheet
     */
    @org.junit.Test
    public void writeWithMultipleSheel(){
        List<String> list2 = Arrays.asList("北京", "西安", "国贸");
        List<String> listSheet = Arrays.asList("工资总表", "大区—团队管理者","工作量汇总");
        //地区名称
        for (String name : list2) {
            ArrayList<ExcelUtil.MultipleSheelPropety> list1 = new ArrayList<>();
            // sheet页签数量
            for(int j = 1; j < 4; j++){
                if ( j== 1) {
                    ArrayList<TableHeaderExcelProperty> list = new ArrayList<>();
                    //几行数据
                    for(int i = 1; i < 4; i++){
                        TableHeaderExcelProperty tableHeaderExcelProperty = new TableHeaderExcelProperty();
                        tableHeaderExcelProperty.setName("cmj" + i);
                        tableHeaderExcelProperty.setName1("cmj" + i+i);
                        tableHeaderExcelProperty.setAge(22 + i);
                        tableHeaderExcelProperty.setSchool("清华大学" + i);
                        list.add(tableHeaderExcelProperty);
                    }
                    Sheet sheet = new Sheet(j, 2);
                    sheet.setSheetName(listSheet.get(j-1));
                    ExcelUtil.MultipleSheelPropety multipleSheelPropety = new ExcelUtil.MultipleSheelPropety();
                    multipleSheelPropety.setData(list);
                    multipleSheelPropety.setSheet(sheet);
                    list1.add(multipleSheelPropety);
                } else if (j== 2) {
                    ArrayList<TableHeaderExcelProperty1> list = new ArrayList<>();
                    //几行数据
                    for(int i = 1; i < 5; i++){
                        TableHeaderExcelProperty1 tableHeaderExcelProperty = new TableHeaderExcelProperty1();
                        tableHeaderExcelProperty.setName("cmj" + i);
                        tableHeaderExcelProperty.setName1("cmj" + i+i);
                        tableHeaderExcelProperty.setAge(22 + i);
                        tableHeaderExcelProperty.setSchool("清华大学" + i);
                        list.add(tableHeaderExcelProperty);
                    }
                    Sheet sheet = new Sheet(j, 2);
                    sheet.setSheetName(listSheet.get(j-1));
                    ExcelUtil.MultipleSheelPropety multipleSheelPropety = new ExcelUtil.MultipleSheelPropety();
                    multipleSheelPropety.setData(list);
                    multipleSheelPropety.setSheet(sheet);
                    list1.add(multipleSheelPropety);
                } else {
                    ArrayList<TableHeaderExcelProperty3> list = new ArrayList<>();
                    //几行数据
                    for(int i = 1; i < 10; i++){
                        TableHeaderExcelProperty3 tableHeaderExcelProperty = new TableHeaderExcelProperty3();
                        tableHeaderExcelProperty.setName("cmj" + i);
                        tableHeaderExcelProperty.setName1("cmj" + i+i);
                        tableHeaderExcelProperty.setAge(22 + i);
                        tableHeaderExcelProperty.setSchool("清华大学" + i);
                        list.add(tableHeaderExcelProperty);
                    }
                    Sheet sheet = new Sheet(j, 2);
                    sheet.setSheetName(listSheet.get(j-1));
                    ExcelUtil.MultipleSheelPropety multipleSheelPropety = new ExcelUtil.MultipleSheelPropety();
                    multipleSheelPropety.setData(list);
                    multipleSheelPropety.setSheet(sheet);
                    list1.add(multipleSheelPropety);
                }
            }
            String fileName = "D:\\wenan\\"+name+".xlsx";
            ExcelUtil.writeWithMultipleSheel(fileName,list1);
        }
    }

    /*******************匿名内部类，实际开发中该对象要提取出去**********************/

    /**
     * @description:
     * @author: chenmingjian
     * @date: 19-4-3 14:44
     */
    @EqualsAndHashCode(callSuper = true)
    @Data
    public static class TableHeaderExcelProperty extends BaseRowModel {

        /**
         * value: 表头名称
         * index: 列的号, 0表示第一列
         */
        @ExcelProperty(value = "姓名", index = 0)
        @ExcelField
        private String name;
        /**
         * value: 表头名称
         * index: 列的号, 0表示第一列
         */
        @ExcelProperty(value = "姓名", index = 0)
        private String name1;

        @ExcelProperty(value = "年龄",index = 2)
        private int age;

        @ExcelProperty(value = "学校",index = 4)
        private String school;


        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        public int getAge() {
            return age;
        }

        public void setAge(int age) {
            this.age = age;
        }

        public String getSchool() {
            return school;
        }

        public void setSchool(String school) {
            this.school = school;
        }

        public String getName1() {
            return name1;
        }

        public void setName1(String name1) {
            this.name1 = name1;
        }
    }
    /*******************匿名内部类，实际开发中该对象要提取出去**********************/



    /**
     * @description:
     * @author: chenmingjian
     * @date: 19-4-3 14:44
     */
    @EqualsAndHashCode(callSuper = true)
    @Data
    public static class TableHeaderExcelProperty1 extends BaseRowModel {

        /**
         * value: 表头名称
         * index: 列的号, 0表示第一列
         */
        @ExcelProperty(value = "工资", index = 0)
        @ExcelField
        private String name;
        /**
         * value: 表头名称
         * index: 列的号, 0表示第一列
         */
        @ExcelProperty(value = "性别", index = 0)
        private String name1;

        @ExcelProperty(value = "学校",index = 2)
        private int age;

        @ExcelProperty(value = "时间",index = 4)
        private String school;


        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        public int getAge() {
            return age;
        }

        public void setAge(int age) {
            this.age = age;
        }

        public String getSchool() {
            return school;
        }

        public void setSchool(String school) {
            this.school = school;
        }

        public String getName1() {
            return name1;
        }

        public void setName1(String name1) {
            this.name1 = name1;
        }
    }




    /**
     * @description:
     * @author: chenmingjian
     * @date: 19-4-3 14:44
     */
    @EqualsAndHashCode(callSuper = true)
    @Data
    public static class TableHeaderExcelProperty3 extends BaseRowModel {

        /**
         * value: 表头名称
         * index: 列的号, 0表示第一列
         */
        @ExcelProperty(value = "工资1", index = 0)
        @ExcelField
        private String name;

        /**
         * value: 表头名称
         * index: 列的号, 0表示第一列
         */
        @ExcelProperty(value = "性别1", index = 0)
        private String name1;

        @ExcelProperty(value = "学校1",index = 2)
        private int age;

        @ExcelProperty(value = "时间1",index = 4)
        private String school;


        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        public int getAge() {
            return age;
        }

        public void setAge(int age) {
            this.age = age;
        }

        public String getSchool() {
            return school;
        }

        public void setSchool(String school) {
            this.school = school;
        }

        public String getName1() {
            return name1;
        }

        public void setName1(String name1) {
            this.name1 = name1;
        }
    }

}